#encoding=utf-8


#python单例模式
#使用__metaclass__（元类）的高级python用法
class Singleton(type):
    def __init__(cls, name, bases, dict):
        super(Singleton, cls).__init__(name, bases, dict)
        cls._instance = None
    def __call__(cls, *args, **kw):
        if cls._instance is None:
            cls._instance = super(Singleton, cls).__call__(*args, **kw)
        return cls._instance

"""
数据库管理类
"""
import sqlalchemy,baseFunc
from sqlalchemy import create_engine
from sqlalchemy import desc
from sqlalchemy import func
from sqlalchemy import Text,DATETIME
# result = db.execute(text('select * from table where id < :id and typeName=:type'), {'id': 2,'type':'USER_TABLE'})

# from sqlalchemy import DateTime
#  date_param=datetime.today()+timedelta(days=-1*10)
#     sql="delete from caw_job_alarm_log  where alarm_time<:alarm_time_param"
#     t=text(sql, bindparams=[bindparam('alarm_time_param', type_=DateTime, required=True)])
#     db.execute(t,{"alarm_time_param": date_param})

from sqlalchemy.orm import sessionmaker
# DB_CONNECT_STRING = 'mysql+mysqldb://crdba:admin135790135790@localhost/Crawlers?charset=utf8'
DB_CONNECT_STRING = 'mysql+mysqldb://crdba:admin135790135790@localhost/Crawlerslocal?charset=utf8'
# DB_CONNECT_STRING = 'mysql+mysqldb://crdba:admin135790135790@localhost/Crawlers_local?charset=utf8'
# engine = create_engine(DB_CONNECT_STRING, echo=True,pool_size=20,max_overflow=30)
# DB_Session = sessionmaker(bind=engine)
# session = DB_Session()
# session.execute('insert into user(name,psd) values(\"aaa",\"bbb")')
# session.commit()
# result=session.execute('select * from USER ')
# rows=result.fetchall();
# result.close()
# print rows
# for row in rows:
#     print row[0],row[1],row[2],row[3],row[4],'id='+str(row['id']),'name='+row['name']
#     print '\r\n'
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String, Float
class User(Base):
     __tablename__ = 'users'

     id = Column(Integer, primary_key=True)
     name = Column(String(300))
     fullname = Column(String(500))
     password = Column(String(200))

     def __init__(self, name, fullname, password):
         self.name = name
         self.fullname = fullname
         self.password = password

     def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

class web_session(Base):
    __tablename__='web_session'
    id=Column(Integer,primary_key=True)
    clientip=Column(String(20))
    key=Column(String(100))
    def __init__(self,clinet_ip,auth_key):
        self.clientip=clinet_ip
        self.key=auth_key
    def __repr__(self):
        return "<web_session('%s','%s')>" % (self.clientip,self.key)

class third_user(Base):
    __tablename__='third_user'
    id=Column(Integer,primary_key=True)
    nick=Column(String(200),index=True)#` varchar(200) DEFAULT NULL COMMENT '加密存储',
    psd=Column(String(200))# varchar(200) DEFAULT NULL COMMENT '加密存储',
    imgUrl=Column(String(255))#` varchar(255) DEFAULT NULL COMMENT '加密存储',
    src=Column(String(255),index=True)# varchar(255) DEFAULT NULL COMMENT '加密存储',
    user_email=Column(String(200))#` varchar(200) DEFAULT NULL COMMENT '加密存储',
    registry=Column(Integer)#` int(11) DEFAULT NULL COMMENT '是否注册成功',
    modify=Column(Integer)#` int(11) DEFAULT NULL COMMENT '是否修改profile成功',

    def __init__(self,nick,psd,imgurl,src,user_email,registry,modify):
        self.nick=nick
        self.psd=psd
        self.imgUrl=imgurl
        self.src=src
        self.user_email=user_email
        self.registry=registry
        self.modify=modify
    def __repr__(self):
        return "<third_user('%s','%s','%s','%s','%s','%s','%s')>" % (self.nick,self.psd,self.imgUrl,self.src,
                                                                     self.user_email,self.registry,self.modify)
class userInfo(Base):
    __tablename__='third_user_info'
    id=Column(Integer,primary_key=True)
    third_id=Column(Integer,index=True)
    userId=Column(String(60),index=True)
    userToken=Column(String(60))
    followCount=Column(Integer)
    # city=Column(String(30))
    def __init__(self,_id,Id,Token,followCount):
        self.third_id=_id
        self.userId=Id
        self.userToken=Token
        self.followCount=followCount
        # self.city=city
    def __repr__(self):
        return "<third_user_info('%s','%s','%s','%s')>" %(self.third_id,self.userId,self.userToken,self.followCount)
class userCity(Base):
    __tablename__='third_user_city'
    id=Column(Integer,primary_key=True)
    userId=Column(String(60),index=True)
    city=Column(String(60))
    def __init__(self,userid,city):
        self.userId=userid
        self.city=city
    def __repr__(self):
        return "<third_user_city('%s','%s')>" % (self.userId,self.city)
class third_user_local(Base):
    __tablename__='third_user_local'
    id=Column(Integer,primary_key=True)
    nick=Column(String(200),index=True)
    weight=Column(Float)
    imgUrl=Column(String(255))
    src=Column(String(255),index=True)
    Heigth=Column(Integer)
    imgLocal=Column(String(255))
    gender=Column(Integer)
    age=Column(Integer)

    def __init__(self,nick,weight,imgurl,src,Heigth,imgLocal,gender,age):
        self.nick=nick
        self.weight=weight*1.0
        self.imgUrl=imgurl
        self.src=src
        self.Heigth=Heigth
        self.imgLocal=imgLocal

        self.gender=gender
        self.age=age
    def __repr__(self):
        return "<third_user_local('%s','%s','%s','%s','%s','%s','%s','%s')>" % (self.nick,self.weight,self.imgUrl,self.src,
                                                                     self.Heigth,self.imgLocal,self.gender,self.age)
class user_back_info(Base):
    __tablename__='user_info_back'
    id=Column(Integer,primary_key=True)
    nick=Column(String(200),index=True)
    psd=Column(String(100))
    user_email=Column(String(200))
    weight=Column(Float)
    imgUrl=Column(String(255))
    src=Column(String(255),index=True)
    Heigth=Column(Integer)
    imgLocal=Column(String(255))
    gender=Column(Integer)
    age=Column(Integer)
    userId=Column(String(60))
    userToken=Column(String(60))
    city=Column(String(60))

    def __init__(self,nick,psd,user_email,weight,imgurl,src,Heigth,imgLocal,gender,age,city,Id,Token):
        self.nick=nick
        self.psd=psd
        self.user_email=user_email
        self.weight=weight*1.0
        self.imgUrl=imgurl
        self.src=src
        self.Heigth=Heigth
        self.imgLocal=imgLocal
        self.gender=gender
        self.age=age
        self.city=city
        self.userId=Id
        self.userToken=Token
    def __repr__(self):
        return "<user_info_back('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')>" % \
               (self.nick,self.psd,self.user_email,self.weight,self.imgUrl,self.src,
                self.Heigth,self.imgLocal,self.gender,self.age,self.userId,self.userToken,self.city)

class myEmail(Base):
    __tablename__='myemails'
    id=Column(Integer,primary_key=True)
    email=Column(String(255))
    psd=Column(String(255))
    def __init__(self,mail,psd):
        self.email=mail
        self.psd=psd
    def __repr__(self):
        return "<myemails('%s','%s')>"%(self.email,self.psd)

class userPlan(Base):
    __tablename__='user_create_plan'
    id=Column(Integer,primary_key=True)
    srctype=Column(Integer)
    baseindex=Column(Integer)
    curindex=Column(Integer)
    stopindex=Column(Integer)
    passcount=Column(Integer)
    def __init__(self,srcindex,baseindex,stopindex):
        self.baseindex=baseindex
        self.srctype=srcindex
        self.curindex=baseindex
        self.stopindex=stopindex
        self.passcount=0
    def __repr__(self):
        return "<user_create_plan('%s','%s','%s','%s','%s')>" % (self.srctype,self.baseindex,self.curindex,self.stopindex,self.passcount)
class userFollow(Base):
    __tablename__='user_follow'
    id=Column(Integer,primary_key=True)
    userid=Column(String(60),index=True)
    usertoken=Column(String(60))
    followid=Column(String(60),index=True)
    followtime=Column(DATETIME)
    def __init__(self,userid,usertoken,followid,followtime):
        self.userid=userid
        self.usertoken=usertoken
        self.followid=followid
        self.followtime=followtime
    def __repr__(self):
        return "<user_follow('%s','%s','%s','%s')>"%(self.userid,self.usertoken,self.followid,self.followtime)
class userDoPlan(Base):
    __tablename__='user_do_plan'
    id=Column(Integer,primary_key=True)
    dotype=Column(Integer) #1、关注 2、点赞  3、自动评论
    srctype=Column(Integer)#1、新用户 2、新发贴 3、新feed
    thisuserid=Column(String(60))#要操作的用户ID(user_id)
    thisusertoken=Column(String(60))#要操作的用户token
    doid=Column(Integer)#对象ID（帖子ID，feed ID）
    strid=Column(String(60))#对象ID（用户ID）
    dotime=Column(Integer)#预计的操作时间，秒
    def __init__(self,dotype,srctype,thisuserid,thisusertoken,doid,strid,dotime):
        self.dotype=dotype
        self.srctype=srctype
        self.thisuserid=thisuserid
        self.thisusertoken=thisusertoken
        self.doid=doid
        self.strid=strid
        self.dotime=dotime
    def __repr__(self):
        return "<user_do_plan('%s','%s','%s','%s','%s','%s','%s')>"%(self.dotype,self.srctype,self.thisuserid,self.thisusertoken,self.doid,self.strid,self.dotime)


class topic_table(Base):
    __tablename__='user_topic'
    id=Column(Integer,primary_key=True)
    src=Column(String(60))#
    srcurl=Column(String(200),index=True)
    rawtext=Column(Text)
    rawtitle=Column(String(60))
    sendtext=Column(Text)
    senduser=Column(String(60))
    sendtime=Column(Integer)
    addtime=Column(Integer)
    def __init__(self,src,srcurl,rawtext,rawtitle,sendtext,senduser,sendtime,addtime):
        self.src=src
        self.srcurl=srcurl
        self.rawtext=rawtext
        self.rawtitle=rawtitle
        self.sendtext=sendtext
        self.senduser=senduser
        self.sendtime=sendtime
        self.addtime=addtime
    def __repr__(self):
        return "<user_topic('%s','%s','%s','%s','%s','%s','%s','%s')>"%\
               (self.src,self.srcurl,self.rawtext,self.rawtitle,self.sendtext,self.senduser,self.sendtime,self.addtime)

class user_session(Base):
    __tablename__='user_session'
    id=Column(Integer,primary_key=True)
    clientip=Column(String(20))
    clientsession=Column(String(60))
    outtime=Column(Integer)
    def __init__(self,remoteip,session,outtime):
        self.clientip=remoteip
        self.clientsession=session
        self.outtime=outtime
    def __repr__(self):
        return "<user_session('%s','%s','%s')>"%(self.clientip,self.clientsession,self.outtime)
class user_base_image(Base):
    __tablename__='user_base_image'
    id=Column(Integer,primary_key=True)
    imgurl=Column(String(255),index=True)
    isused=Column(Integer)
    def __init__(self,url):
        self.imgurl=url
        self.isused=0
    def __repr__(self):
        return "<user_base_image('%s','%s')>"%(self.imgurl,self.isused)
class user_base_name(Base):
    __tablename__='user_base_name'
    id=Column(Integer,primary_key=True)
    name=Column(String(20),index=True)
    isused=Column(Integer)
    def __init__(self,name):
        self.name=name
        self.isused=0
    def __repr__(self):
        return "<user_base_image('%s','%s')>"%(self.name,self.isused)

class base_index(Base):
    __tablename__='base_index'
    id=Column(Integer,primary_key=True)
    name=Column(String(20) ,index=True)
    isused=Column(Integer)
    def __init__(self,url):
        self.imgurl=url
        self.isused=0
    def __repr__(self):
        return "<base_index('%s','%s')>"%(self.name,self.isused)

class MyListener(sqlalchemy.interfaces.PoolListener):
    def __init__(self):
       self.retried = False
    def checkout(self, dbapi_con, con_record, con_proxy):
        iCount=0
        while iCount<3:
            try:
                dbapi_con.info()
                return
            except:
                pass
            iCount+=1
        try:
            dbapi_con.info() # is there any better way to simply check if connection to mysql is alive?
        except sqlalchemy.exc.OperationalError:
            if self.retried:
                self.retried = False
                raise # we do nothing
            self.retried = True
            raise sqlalchemy.exc.DisconnectionError

class mysql_db():
    __metaclass__ = Singleton
    def __init__(self):
        from sqlalchemy.pool import NullPool
        baseFunc.checkislocal()
        db_connect_setting=DB_CONNECT_STRING
        if baseFunc.isrunning_local.value!=1:
            db_connect_setting = 'mysql+mysqldb://crdba:admin135790135790@localhost/Crawlers?charset=utf8'
        # self.engine = create_engine(DB_CONNECT_STRING, echo=False,pool_size=200,max_overflow=230,pool_recycle=24*3600,listeners=[MyListener()])
        self.engine = create_engine(db_connect_setting, echo=False,poolclass=NullPool)
        Base.metadata.create_all(self.engine)
        self.db_session = sessionmaker(bind=self.engine)
        self._sql_execute("SET NAMES 'utf8';")
        self._sql_execute("SET interactive_timeout=24*3600;")
        self.setPlanBase()
    def setPlanBase(self):
        pbSession=self.__get_session()
        pbQuery=pbSession.query(userPlan)
        pbSJJY=pbQuery.filter(userPlan.srctype==0x1001).first()
        if not pbSJJY:#3330825
            pbSJJY=userPlan(0x1001,1000002,137000000)
            pbSession.add(pbSJJY)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'jiayuan')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=2:
                    pbSJJY.curindex=int(sList[len(sList)-1])+1
                    pbSession.commit()
            #select src from third_user_local where INSTR(src,'jiayuan')>0 order by src desc limit 1;
            pass
        pbBoohee=pbQuery.filter(userPlan.srctype==0x1002).first()
        if not pbBoohee:#1340
            pbBoohee=userPlan(0x1002,100,10400000)
            pbSession.add(pbBoohee)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'boohee')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=4:
                    pbBoohee.curindex=int(sList[len(sList)-1])+1
                    pbSession.commit()
        pbBaihe=pbQuery.filter(userPlan.srctype==0x1003).first()
        if not pbBaihe:#99978
            pbBaihe=userPlan(0x1003,403,120000000)
            pbSession.add(pbBaihe)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'baihe')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('=')
                if len(sList)>=2:
                    pbBaihe.curindex=int(sList[len(sList)-1])+1
                    pbSession.commit()
                pbBaihe=pbQuery.filter(userPlan.srctype==0x1003).first()
        pbHupu=pbQuery.filter(userPlan.srctype==0x1008).first()
        if not pbHupu:#99978
            pbHupu=userPlan(0x1008,100,5450060)
            pbSession.add(pbHupu)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'hupu.com')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=2:
                    pbHupu.curindex=int(sList[len(sList)-1])+1
                    pbSession.commit()

        pbDouban=pbQuery.filter(userPlan.srctype==0x1009).first()
        if not pbDouban:#99978
            pbDouban=userPlan(0x1009,100100,503200)
            pbSession.add(pbDouban)
            pbSession.commit()
        # else:
        #     srs=self._sql_open("select src from third_user_local where INSTR(src,'douban.com')>0 order by src desc limit 1")
        #     for sItem in srs :
        #         print sItem['src']
        #         sSrc=sItem['src']
        #         sList=sSrc.split('/')
        #         print sList
        #         if len(sList)>=2:
        #             pbDouban.curindex=int(sList[1])+1
        #             pbSession.commit()
        pbTianya=pbQuery.filter(userPlan.srctype==0x100A).first()
        if not pbTianya:#99978
            pbTianya=userPlan(0x100A,100000,5450060)
            pbSession.add(pbTianya)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'www.tianya.cn')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=2:
                    pbTianya.curindex=int(sList[len(sList)-1])+1
                    pbSession.commit()
        pbMop=pbQuery.filter(userPlan.srctype==0x100B).first()
        if not pbMop:#99978
            pbMop=userPlan(0x100B,100000,5450060)
            pbSession.add(pbMop)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'hi.mop.com')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=2:
                    pbMop.curindex=int(sList[len(sList)-1])+1
                    pbSession.commit()
        pbsodao=pbQuery.filter(userPlan.srctype==0x100C).first()
        if not pbsodao:#99978
            pbsodao=userPlan(0x100C,319001,100000000)
            pbSession.add(pbsodao)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'i.sodao.com')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=2:
                    sint=sList[len(sList)-1]
                    pbsodao.curindex=int(sint[1:])+1
                    pbSession.commit()

        pbyouyuan=pbQuery.filter(userPlan.srctype==0x100D).first()
        if not pbyouyuan:#99978
            pbyouyuan=userPlan(0x100D,10000,100000000)
            pbSession.add(pbyouyuan)
            pbSession.commit()
        else:
            srs=self._sql_open("select src from third_user_local where INSTR(src,'www.youyuan.com')>0 order by src desc limit 1")
            for sItem in srs :
                print sItem['src']
                sSrc=sItem['src']
                sList=sSrc.split('/')
                if len(sList)>=2:
                    sint=sList[len(sList)-1]
                    silist=sint.split('-')
                    pbyouyuan.curindex=int(silist[0])+1
                    pbSession.commit()

        pbReg=pbQuery.filter(userPlan.srctype==0x1004).first()
        if not pbReg:
            pbReg=userPlan(0x1004,1,2000000000)
            pbSession.add(pbReg)
            pbSession.commit()
        else:
            srs=self._sql_open("select max(id) from third_user_info")
            for sItem in srs :
                sSrc=sItem[0]
                try:
                    pbReg.curindex=int(sSrc)
                except:
                    pbReg.curindex=0
                pbSession.commit()

        pbSession.close()
        # pbSession.remove()
    def __get_session(self):
        return self.db_session()
    def _sql_execute(self,sql):
        session=self.__get_session()
        session.execute(sql)
        session.commit()
        session.close()
    def _sql_open(self,sql):
        session=self.__get_session()
        result=session.execute(sql)
        rows=result.fetchall()
        result.close()
        session.close()
        return rows
    def _obj_query(self,obj):
        session=self.__get_session()
        return  session,session.query(obj)
    def _obj_add_commit(self,obj):
        session=self.__get_session()
        session.add(obj)
        session.commit()
        session.close()
    def _ramdon_choice_user(self,minid=382668):
        session=self.__get_session()
        user=session.query(userInfo).filter(userInfo.id>minid).order_by(func.random()).limit(1).first()
        session.close()
        return user



if __name__=="__main__":
    xparams={"a":1,"b":2}
    import  json
    xstr=json.dumps(xparams)
    ijson=json.loads(xstr)
    print ijson['a'], ijson.get('c','aaaaa'),'\r\n'
    xx=mysql_db()
    _topic=xx._obj_query(topic_table)
    ss="%"+"跑"+"%"
    _list=  _topic[1].filter(topic_table.rawtext.like(ss)).all()
    print len(_list)
    srs=xx._sql_open("select src from third_user_local where INSTR(src,'jiayuan')>0 order by src desc limit 1")
    for sItem in srs :
        print sItem['src']
        print sItem['src']
        sSrc=sItem['src']
        sList=sSrc.split('/')
        print  sList
    xx_Local=xx._obj_query(third_user_local)


    sPlan="INSTR(src,\"jiayuan\")>0".decode().encode('utf8')
    print xx_Local[1].filter(third_user_local.src.like('www.jiayuan%')).order_by(desc(third_user_local.src)).first()

    xxxUser=third_user_local('aaaaa',444.5,'swwww','wwwww.dddd.sss',112,30,1,1)
    xuser=User('aa1','aa1 name','apsd')
    xx._obj_add_commit(xuser)
    # xuser.update(name='aasdasdasa11')
    # xuser.name='aasdasdasa11 '
    xxxxx=xx._obj_query(User)
    _user= xxxxx[1].filter(User.id==6).first()
    if _user:
        _user.name='sswweeeeee'
        xxxxx[0].commit()
    print  xuser.id
    _user= xx._obj_query(User)[1].filter(User.id==100).first()
    if not _user:
        print 1,_user.id
    xlist=xx._obj_query(User)[1];
    xItemList= xlist.filter(User.id>=5).all()#list  //first()=> one
    for xItem in xItemList:
        print xItem.id,xItem.name,xItem.fullname,xItem.password
    for _user_item in xlist:
        print _user_item.id, _user_item.name,_user_item.fullname,_user_item.password

    xx._sql_execute('insert INTO USER(name,psd) VALUES(\"a2222",\"b22222")')
    rows=xx._sql_open('select * from USER');
    for row in rows:
        print row[0],row[1],row[2],row[3],row[4],'id='+str(row['id']),'name='+row['name']
        print '\r\n'